package com.nananer7.dao;

import com.nananer7.DBAccess;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class IdeaDao {
    public int getIid() {
        return iid;
    }

    public void setIid(int iid) {
        this.iid = iid;
    }

    private int iid;

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    private int uid;

    public String getSubject() {
        return subject;
    }

    public void setSubject(String subject) {
        this.subject = subject;
    }

    private String subject;

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    private String description;

    public String getCategories() {
        return categories;
    }

    public void setCategories(String categories) {
        this.categories = categories;
    }

    private String categories;

    public int getLikes() {
        return likes;
    }

    public void setLikes(int likes) {
        this.likes = likes;
    }

    private int likes;

    public Timestamp getTime() {
        return time;
    }

    public void setTime(Timestamp time) {
        this.time = time;
    }

    private Timestamp time;

    //无参构造函数
    public IdeaDao(){

    }

    public IdeaDao(int iid, int uid,String subject,String description,String categories,int likes,Timestamp time) {
        super();
        this.iid=iid;
        this.uid=uid;
        this.subject=subject;
        this.description=description;
        this.categories=categories;
        this.time=time;
        this.likes=likes;

    }


    //添加新的idea
    public boolean addIdea(int uid,String subject,String categories,String description){
        Connection con =null;
        PreparedStatement pstmt =null;
        try {
            con = DBAccess.getConnection();
            String sql = "INSERT INTO idea(uid,subject,categories,description) VALUES(?,?,?,?)";
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,uid);
            pstmt.setString(2, subject);
            pstmt.setString(3, categories);
            pstmt.setString(4, description);
            if(pstmt.executeUpdate()>=1){
                return true;
            }else{
                return false;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt!=null)
                    pstmt.close();
                if(con!=null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    //根据"喜欢"降序显示
    public List<IdeaDao> readByLikes(){
        List list = new ArrayList<IdeaDao>();
        Connection con =null;
        PreparedStatement pstmt =null;
        ResultSet rs = null;

        try {

            con = DBAccess.getConnection();
            String sql="select * from idea order by likes DESC";
            pstmt=con.prepareStatement(sql);
            rs=pstmt.executeQuery();

            while(rs.next()){

                IdeaDao ideaDao = new IdeaDao();

                ideaDao.setIid(rs.getInt(1));
                ideaDao.setUid(rs.getInt(2));
                ideaDao.setSubject(rs.getString(3));
                ideaDao.setDescription(rs.getString(4));
                ideaDao.setCategories(rs.getString(5));
                ideaDao.setLikes(rs.getInt(6));
                ideaDao.setTime(rs.getTimestamp(7));

                list.add(ideaDao);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    //根据"时间"降序显示
    public List<IdeaDao> readByTime(){

        List list = new ArrayList<IdeaDao>();
        Connection con =null;
        PreparedStatement pstmt =null;
        ResultSet rs = null;

        try {

            con = DBAccess.getConnection();
            String sql="select * from idea order by time DESC";
            pstmt=con.prepareStatement(sql);
            rs=pstmt.executeQuery();

            while(rs.next()){

                IdeaDao ideaDao = new IdeaDao();

                ideaDao.setIid(rs.getInt(1));
                ideaDao.setUid(rs.getInt(2));
                ideaDao.setSubject(rs.getString(3));
                ideaDao.setDescription(rs.getString(4));
                ideaDao.setCategories(rs.getString(5));
                ideaDao.setLikes(rs.getInt(6));
                ideaDao.setTime(rs.getTimestamp(7));

                list.add(ideaDao);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    //模糊查询
    public List<IdeaDao> search(String s){
        String str="%";
        String search = str+s+str;

        List list = new ArrayList<IdeaDao>();
        Connection con =null;
        PreparedStatement pstmt =null;
        ResultSet rs = null;

        try {

            con = DBAccess.getConnection();
            String sql="select * from idea where subject LIKE ? order by time DESC";
            pstmt=con.prepareStatement(sql);
            pstmt.setString(1, search);
            rs=pstmt.executeQuery();

            while(rs.next()){

                IdeaDao ideaDao = new IdeaDao();

                ideaDao.setIid(rs.getInt(1));
                ideaDao.setUid(rs.getInt(2));
                ideaDao.setSubject(rs.getString(3));
                ideaDao.setDescription(rs.getString(4));
                ideaDao.setCategories(rs.getString(5));
                ideaDao.setLikes(rs.getInt(6));
                ideaDao.setTime(rs.getTimestamp(7));

                list.add(ideaDao);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    //根据iid查找subject
    public String findSubjectByIid(int iid){
        String subject = null;
        Connection con =null;
        PreparedStatement pstmt =null;
        ResultSet rs = null;
        try {
            con = DBAccess.getConnection();
            pstmt = con.prepareStatement("select * from idea where iid=?");
            pstmt.setInt(1, iid);
            rs = pstmt.executeQuery();
            if(rs==null){
                return null;
            }
            if(rs.next()){
                subject=rs.getString("subject");
            }else{
                subject=null;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt!=null)pstmt.close();
                if(con!=null)con.close();
            }
            catch (SQLException e) {
            }
        }
        return subject;
    }
    //根据iid查找description
    public String findDescriptionByIid(int iid){
        String description = null;
        Connection con =null;
        PreparedStatement pstmt =null;
        ResultSet rs = null;
        try {
            con = DBAccess.getConnection();
            pstmt = con.prepareStatement("select * from idea where iid=?");
            pstmt.setInt(1, iid);
            rs = pstmt.executeQuery();
            if(rs==null){
                return null;
            }
            if(rs.next()){
                description=rs.getString("description");
            }else{
                description=null;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt!=null)pstmt.close();
                if(con!=null)con.close();
            }
            catch (SQLException e) {
            }
        }
        return description;
    }
    //根据iid查找categories
    public String findCategoriesByIid(int iid){
        String categories = null;
        Connection con =null;
        PreparedStatement pstmt =null;
        ResultSet rs = null;
        try {
            con = DBAccess.getConnection();
            pstmt = con.prepareStatement("select * from idea where iid=?");
            pstmt.setInt(1, iid);
            rs = pstmt.executeQuery();
            if(rs==null){
                return null;
            }
            if(rs.next()){
                categories=rs.getString("categories");
            }else{
                categories=null;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt!=null)pstmt.close();
                if(con!=null)con.close();
            }
            catch (SQLException e) {
            }
        }
        return categories;
    }
    //根据iid查找likes
    public int findLikesByIid(int iid){
        int likes = 0;
        Connection con =null;
        PreparedStatement pstmt =null;
        ResultSet rs = null;
        try {
            con = DBAccess.getConnection();
            pstmt = con.prepareStatement("select * from idea where iid=?");
            pstmt.setInt(1, iid);
            rs = pstmt.executeQuery();
            if(rs==null){
                return -1;
            }
            if(rs.next()){
                likes=rs.getInt("likes");
            }else{
                likes=0;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt!=null)pstmt.close();
                if(con!=null)con.close();
            }
            catch (SQLException e) {
            }
        }
        return likes;
    }
    //根据iid查找time
    public Timestamp findTimeByIid(int iid){
        Timestamp time = null;
        Connection con =null;
        PreparedStatement pstmt =null;
        ResultSet rs = null;
        try {
            con = DBAccess.getConnection();
            pstmt = con.prepareStatement("select * from idea where iid=?");
            pstmt.setInt(1, iid);
            rs = pstmt.executeQuery();
            if(rs==null){
                return null;
            }
            if(rs.next()){
                time=rs.getTimestamp("time");
            }else{
                time = null;
            }
        }catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(pstmt!=null)pstmt.close();
                if(con!=null)con.close();
            }
            catch (SQLException e) {
            }
        }
        return time;
    }

    //修改月份格式
    public String changeMon(int month){
        String MON=null;
        switch (month){
            case 1:
                MON = "Jan";
                break;
            case 2:
                MON = "Feb";
                break;
            case 3:
                MON = "Mar";
                break;
            case 4:
                MON = "Apr";
                break;
            case 5:
                MON = "May";
                break;
            case 6:
                MON = "June";
                break;
            case 7:
                MON = "July";
                break;
            case 8:
                MON = "Aug";
                break;
            case 9:
                MON = "Sept";
                break;
            case 10:
                MON = "Oct";
                break;
            case 11:
                MON = "Nov";
                break;
            case 12:
                MON = "Dec";
                break;
            default:
                MON = "error";
                break;
        }
        return MON;
    }

    //单独测试使用
    /*  public static void main(String[] args) {

        IdeaDao ideaDao = new IdeaDao();

        List<IdeaDao> ideaDaos= ideaDao.search("共享");

        for(int i=0;i<ideaDaos.size();i++){

            int iid= ideaDaos.get(i).getIid();
            int uid=ideaDaos.get(i).getUid();
            String subject = ideaDaos.get(i).getSubject();
            String description = ideaDaos.get(i).getDescription();

            System.out.println(iid+" "+uid+" "+subject+" "+description);
        }
    }*/
}
